Re: index file bloating still in 7.4 ? - Mailing list pgsql-performance

Hi Tom,

The key is a range from 1 to 30000 and picked randomly.

Oh, so in order to reclaim the disk space, we must run
reindex or vacuum full ?
This will lock out the table and we won't be able to do anything.
Looks like this is a problem. It means we cannot use it for
24x7 operations without having to stop the process and do the vacuum full
and reindex. Is there anything down the road that these operations
will not lock out the table ?

I let the process ran overnight. The last email I sent you with
the vacuum analyze output just about an hour ago, that was after
I removed the process that does the updates.

However, I search through all the vacuum I did just before I
went to bed and found that earlier vacuum did say 5 indexes deleted and
5 reusable. It has been pretty constant for about 1 to 2 hours and
then down to zero and has been like this since.

Sun Oct 19 00:50:07 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 402335 row versions in 7111 pages
DETAIL:  5 index pages have been deleted, 5 are currently reusable.
CPU 1.32s/0.17u sec elapsed 22.44 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 401804 nonremovable
row versions in 35315 pages
DETAIL:  101802 dead row versions cannot be removed yet.
There were 1646275 unused item pointers.
0 pages are entirely empty.
CPU 2.38s/0.71u sec elapsed 27.09 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 156124
estimated total rows
VACUUM
Sleep 60 seconds

Sun Oct 19 00:51:40 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 411612 row versions in 7111 pages
DETAIL:  5 index pages have been deleted, 5 are currently reusable.
CPU 1.28s/0.22u sec elapsed 23.38 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 410889 nonremovable
row versions in 35315 pages
DETAIL:  110900 dead row versions cannot be removed yet.
There were 1637190 unused item pointers.
0 pages are entirely empty.
CPU 2.13s/0.92u sec elapsed 27.13 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 123164
estimated total rows
VACUUM
Sleep 60 seconds
.
.
.
Sun Oct 19 02:14:41 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 1053582 row versions in 7112 pages
DETAIL:  5 index pages have been deleted, 5 are currently reusable.
CPU 0.58s/0.29u sec elapsed 21.63 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 1053103 nonremovable
row versions in 35315 pages
DETAIL:  753064 dead row versions cannot be removed yet.
There were 995103 unused item pointers.
0 pages are entirely empty.
CPU 1.54s/1.35u sec elapsed 26.17 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627
estimated total rows
VACUUM
Sleep 60 seconds

Sun Oct 19 02:16:16 CDT 2003
INFO:  vacuuming "scncraft.dsperf_rda_or_key"
INFO:  index "dsperf242_1105" now contains 1065887 row versions in 7119 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.71s/0.36u sec elapsed 21.12 sec.
INFO:  "dsperf_rda_or_key": found 0 removable, 1065357 nonremovable
row versions in 35315 pages
DETAIL:  765328 dead row versions cannot be removed yet.
There were 982849 unused item pointers.
0 pages are entirely empty.
CPU 1.70s/1.42u sec elapsed 26.65 sec.
INFO:  analyzing "scncraft.dsperf_rda_or_key"
INFO:  "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627
estimated total rows
VACUUM
Sleep 60 seconds
.
.
.

Thanks.
Gan


At 11:47 am -0400 2003/10/19, Tom Lane wrote:
>Seum-Lim Gan <slgan@lucent.com> writes:
>>  vacuum verbose analyze dsperf_rda_or_key;
>>  INFO:  vacuuming "scncraft.dsperf_rda_or_key"
>>  INFO:  index "dsperf242_1105" now contains 300000 row versions in
>>12387 pages
>>  DETAIL:  3097702 index row versions were removed.
>  > 0 index pages have been deleted, 0 are currently reusable.
>
>Hm, interesting that you deleted 90% of the entries and still had no
>empty index pages at all.  What was the pattern of your deletes and/or
>updates with respect to this index's key?
>
>>  However, when I check the disk space usage, it has not changed.
>
>It won't in any case.  Plain VACUUM is designed for maintaining a
>steady-state level of free space in tables and indexes, not for
>returning major amounts of space to the OS.  For that you need
>more-invasive operations like VACUUM FULL or REINDEX.
>
>            regards, tom lane


--
+--------------------------------------------------------+
| Seum-Lim GAN                 email : slgan@lucent.com  |
| Lucent Technologies                                    |
| 2000 N. Naperville Road, 6B-403F  tel : (630)-713-6665 |
| Naperville, IL 60566, USA.        fax : (630)-713-7272 |
|       web : http://inuweb.ih.lucent.com/~slgan         |
+--------------------------------------------------------+